package cc.bookcloud.tool.ocr.mapper;
import cc.bookcloud.tool.ocr.domain.TableFileds;
import cc.bookcloud.tool.ocr.domain.Tables;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mapstruct.Mapper;

import java.util.List;

/**
 * <p> 查询表数据信息 </p>
 *
 * @author : zhaoqiuyue
 * @description :
 * @date : 2019/11/8 15:46
 */
@Mapper
public interface TableMapper {

    /**
     * 获取指定数据库下所有表名和注释
     *
     * @param dbName:数据库名
     * @return: java.util.List<com.mpolicy.dbfile.modules.system.entity.Tables>
     */

    @Select("<script> SELECT t1.TABLE_NAME,t2.COMMENTS FROM ALL_TABLES t1 LEFT JOIN user_tab_comments t2\n" +
            "    on(t1.TABLE_NAME=t2.TABLE_NAME) WHERE 1=1 AND t1.OWNER = #{dbName} " +
            "AND (t1.TABLE_NAME like concat('ELE','%') or t1.TABLE_NAME like concat('BAS','%'))" +
            "<if test=\"tableList != null and tableList.size()>0 \">"+
            "and t1.table_name in " +
            "<foreach collection= 'tableList' item= 'tableName' open='(' separator= ',' close=')'> +" +
            "#{tableName}" +
            "</foreach>" +
            "</if>"+
            "</script>")
    List<Tables> getAllTables(@Param("dbName") String dbName, @Param("tableList") List list);

    /**
     * 获取指定表信息
     *
     * @param tableName:表
     * @return: java.util.List<com.mpolicy.dbfile.modules.system.entity.TableFileds>
     */
    @Select("SELECT\n" +
            "    c.*,d.CONSTRAINT_TYPE\n" +
            "FROM\n" +
            "    (\n" +
            "        SELECT\n" +
            "            a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.OWNER,a.DATA_LENGTH,b.COMMENTS\n" +
            "        FROM\n" +
            "            all_tab_columns a,all_col_comments b\n" +
            "        WHERE\n" +
            "                a.TABLE_NAME = b.TABLE_NAME AND a.OWNER = b.OWNER AND a.COLUMN_NAME = b.COLUMN_NAME\n" +
            "          AND a.table_name =  #{tableName}\n" +
            "          AND a.OWNER =#{dbName}\n" +
            "    ) c\n" +
            "        LEFT JOIN (\n" +
            "        SELECT\n" +
            "            a.constraint_name,a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,b.CONSTRAINT_TYPE\n" +
            "        FROM\n" +
            "            all_cons_columns a,all_constraints b\n" +
            "        WHERE\n" +
            "                a.constraint_name = b.constraint_name AND a.table_name =  #{tableName}\n" +
            "          AND a.OWNER = #{dbName}\n" +
            "    ) d ON c.COLUMN_NAME = d.COLUMN_NAME\n" +
            "        AND c.OWNER = d.OWNER\n" +
            "        AND c.TABLE_NAME = d.TABLE_NAME")
    List<TableFileds> getTableInfo(@Param("tableName") String tableName, @Param("dbName") String dbName);

}
